{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "pe_data = pd.read_excel(\"投资收益压力测试.xlsx\",sheet_name='投资收益压力测试',usecols=['pe','分位','price'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pe</th>\n",
       "      <th>分位</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50.096901</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1986.539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51.850601</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2055.016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51.324100</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>2035.651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>52.444199</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2085.403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>54.230099</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2159.638</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          pe        分位     price\n",
       "0  50.096901  1.000000  1986.539\n",
       "1  51.850601  1.000000  2055.016\n",
       "2  51.324100  0.666667  2035.651\n",
       "3  52.444199  1.000000  2085.403\n",
       "4  54.230099  1.000000  2159.638"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pe_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "####首先，以2007-2011为模拟回测市场前期，从2012到2019年开始回测，index从1212开始"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "def back_test(df:pd.DataFrame,low,high,time=1):##low，high为分位区间端点,time为投资时间,以月为单位\n",
    "    gains = []\n",
    "    for index,row in tqdm(df.iterrows()):\n",
    "        if row['分位']>low and row['分位']<=high:\n",
    "            next_index = index+time*20\n",
    "            if index+time*20>df.index[-1]:\n",
    "                next_index = df.index[-1]\n",
    "            gains.append(df['price'][next_index]/df['price'][index]-1)###将每一次收益添加至gains\n",
    "    ###返回值为gains的平均值，还有胜率\n",
    "    temp_array = np.array(gains)\n",
    "    temp_array[temp_array>0] = 1\n",
    "    temp_array[temp_array<0] = 0\n",
    "    return np.mean(gains),np.mean(temp_array)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "resul_pe = pd.DataFrame(index=[0.25,0.5,0.75,1],columns=[1,3,6,12,18,24,32,36])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "1953it [00:00, 10528.16it/s]\n",
      "1953it [00:00, 12718.48it/s]\n",
      "1953it [00:00, 12796.56it/s]\n",
      "1953it [00:00, 14994.16it/s]\n",
      "1953it [00:00, 10879.93it/s]\n",
      "1953it [00:00, 13054.62it/s]\n",
      "1953it [00:00, 12085.58it/s]\n",
      "1953it [00:00, 14925.26it/s]\n",
      "1953it [00:00, 10304.48it/s]\n",
      "1953it [00:00, 12087.87it/s]\n",
      "1953it [00:00, 12087.74it/s]\n",
      "1953it [00:00, 12798.94it/s]\n",
      "1953it [00:00, 10585.91it/s]\n",
      "1953it [00:00, 12883.06it/s]\n",
      "1953it [00:00, 12715.79it/s]\n",
      "1953it [00:00, 14610.65it/s]\n",
      "1953it [00:00, 10250.58it/s]\n",
      "1953it [00:00, 11940.61it/s]\n",
      "1953it [00:00, 11898.57it/s]\n",
      "1953it [00:00, 15295.22it/s]\n",
      "1953it [00:00, 10604.29it/s]\n",
      "1953it [00:00, 12633.70it/s]\n",
      "1953it [00:00, 12549.99it/s]\n",
      "1953it [00:00, 15666.16it/s]\n",
      "1953it [00:00, 10940.10it/s]\n",
      "1953it [00:00, 13405.29it/s]\n",
      "1953it [00:00, 12882.76it/s]\n",
      "1953it [00:00, 14190.02it/s]\n",
      "1953it [00:00, 10937.82it/s]\n",
      "1953it [00:00, 12310.05it/s]\n",
      "1953it [00:00, 12967.93it/s]\n",
      "1953it [00:00, 15541.39it/s]\n"
     ]
    }
   ],
   "source": [
    "for i in [1,3,6,12,18,24,32,36]:\n",
    "    for low,high in [(0,0.25),(0.25,0.5),(0.5,0.75),(0.75,1)]:\n",
    "        gain,acc = back_test(pe_data.loc[1212:,:],low,high,i)\n",
    "        resul_pe[i][high] = gain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "resul_pe.to_excel(\"pe收益.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>3</th>\n",
       "      <th>6</th>\n",
       "      <th>12</th>\n",
       "      <th>18</th>\n",
       "      <th>24</th>\n",
       "      <th>32</th>\n",
       "      <th>36</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0.25</th>\n",
       "      <td>0.00960576</td>\n",
       "      <td>0.0185952</td>\n",
       "      <td>0.0365272</td>\n",
       "      <td>0.169594</td>\n",
       "      <td>0.256807</td>\n",
       "      <td>0.384013</td>\n",
       "      <td>0.516114</td>\n",
       "      <td>0.537217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.50</th>\n",
       "      <td>0.0063235</td>\n",
       "      <td>0.0247229</td>\n",
       "      <td>0.0869424</td>\n",
       "      <td>0.0910022</td>\n",
       "      <td>0.0914015</td>\n",
       "      <td>0.0342303</td>\n",
       "      <td>0.00897855</td>\n",
       "      <td>0.03587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0.75</th>\n",
       "      <td>0.0383956</td>\n",
       "      <td>0.151389</td>\n",
       "      <td>0.0895366</td>\n",
       "      <td>-0.00321431</td>\n",
       "      <td>-0.0249166</td>\n",
       "      <td>-0.0223733</td>\n",
       "      <td>-0.0838592</td>\n",
       "      <td>-0.164961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.00</th>\n",
       "      <td>-0.0330025</td>\n",
       "      <td>-0.155143</td>\n",
       "      <td>-0.224115</td>\n",
       "      <td>-0.288224</td>\n",
       "      <td>-0.262933</td>\n",
       "      <td>-0.279928</td>\n",
       "      <td>-0.289906</td>\n",
       "      <td>-0.350876</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              1          3          6           12         18         24  \\\n",
       "0.25  0.00960576  0.0185952  0.0365272    0.169594   0.256807   0.384013   \n",
       "0.50   0.0063235  0.0247229  0.0869424   0.0910022  0.0914015  0.0342303   \n",
       "0.75   0.0383956   0.151389  0.0895366 -0.00321431 -0.0249166 -0.0223733   \n",
       "1.00  -0.0330025  -0.155143  -0.224115   -0.288224  -0.262933  -0.279928   \n",
       "\n",
       "              32        36  \n",
       "0.25    0.516114  0.537217  \n",
       "0.50  0.00897855   0.03587  \n",
       "0.75  -0.0838592 -0.164961  \n",
       "1.00   -0.289906 -0.350876  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "resul_pe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
